{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = 'all'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.读写CSV数据\n",
    "\n",
    "于大多数的CSV格式的数据读写问题，都可以使用 csv 库。 \n",
    "\n",
    "如果你读取CSV数据的目的是做数据分析和统计的话， 你可能需要看一看 Pandas 包。Pandas 包含了一个非常方便的函数叫 pandas.read_csv() ， 它可以加载CSV数据到一个 DataFrame 对象中去。 然后利用这个对象你就可以生成各种形式的统计、过滤数据以及执行其他高级操作了。\n",
    "\n",
    "```\n",
    "Symbol,Price,Date,Time,Change,Volume\n",
    "\"AA\",39.48,\"6/11/2007\",\"9:36am\",-0.18,181800\n",
    "\"AIG\",71.38,\"6/11/2007\",\"9:36am\",-0.15,195500\n",
    "\"AXP\",62.58,\"6/11/2007\",\"9:36am\",-0.46,935000\n",
    "\"BA\",98.31,\"6/11/2007\",\"9:36am\",+0.12,104800\n",
    "\"C\",53.08,\"6/11/2007\",\"9:36am\",-0.25,360900\n",
    "\"CAT\",78.29,\"6/11/2007\",\"9:36am\",-0.23,225400\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['AA', '39.48', '6/11/2007', '9:36am', '-0.18', '181800']\n",
      "['AIG', '71.38', '6/11/2007', '9:36am', '-0.15', '195500']\n",
      "['AXP', '62.58', '6/11/2007', '9:36am', '-0.46', '935000']\n",
      "['BA', '98.31', '6/11/2007', '9:36am', '+0.12', '104800']\n",
      "['C', '53.08', '6/11/2007', '9:36am', '-0.25', '360900']\n",
      "['CAT', '78.29', '6/11/2007', '9:36am', '-0.23', '225400']\n"
     ]
    }
   ],
   "source": [
    "import csv\n",
    "with open('stocks.csv') as f:\n",
    "    f_csv = csv.reader(f)\n",
    "    headers = next(f_csv)\n",
    "    for row in f_csv:\n",
    "        # 在上面的代码中， row 会是一个列表。\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AA\n",
      "AIG\n",
      "AXP\n",
      "BA\n",
      "C\n",
      "CAT\n"
     ]
    }
   ],
   "source": [
    "# 由于这种下标访问通常会引起混淆，你可以考虑使用命名元组。\n",
    "\n",
    "from collections import namedtuple\n",
    "\n",
    "with open('stocks.csv') as f:\n",
    "    f_csv = csv.reader(f)\n",
    "    headers = next(f_csv)\n",
    "    Row = namedtuple('Row', headers)\n",
    "    for r in f_csv:\n",
    "        row = Row(*r)\n",
    "        print(row.Symbol)\n",
    "        # etc..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "AA\n",
      "AIG\n",
      "AXP\n",
      "BA\n",
      "C\n",
      "CAT\n"
     ]
    }
   ],
   "source": [
    "# 另外一个选择就是将数据读取到一个字典序列中去。\n",
    "import csv\n",
    "with open('stocks.csv') as f:\n",
    "    f_csv = csv.DictReader(f)\n",
    "    for row in f_csv:\n",
    "        # process row\n",
    "        print(row['Symbol'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 普通的写入csv\n",
    "headers = ['Symbol','Price','Date','Time','Change','Volume']\n",
    "rows = [('AA', 39.48, '6/11/2007', '9:36am', -0.18, 181800),\n",
    "         ('AIG', 71.38, '6/11/2007', '9:36am', -0.15, 195500),\n",
    "         ('AXP', 62.58, '6/11/2007', '9:36am', -0.46, 935000),\n",
    "       ]\n",
    "\n",
    "with open('stocks.csv','w') as f:\n",
    "    f_csv = csv.writer(f)\n",
    "    f_csv.writerow(headers)\n",
    "    f_csv.writerows(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 字典方式写入csv\n",
    "headers = ['Symbol', 'Price', 'Date', 'Time', 'Change', 'Volume']\n",
    "rows = [{'Symbol':'AA', 'Price':39.48, 'Date':'6/11/2007',\n",
    "        'Time':'9:36am', 'Change':-0.18, 'Volume':181800},\n",
    "        {'Symbol':'AIG', 'Price': 71.38, 'Date':'6/11/2007',\n",
    "        'Time':'9:36am', 'Change':-0.15, 'Volume': 195500},\n",
    "        {'Symbol':'AXP', 'Price': 62.58, 'Date':'6/11/2007',\n",
    "        'Time':'9:36am', 'Change':-0.46, 'Volume': 935000},\n",
    "        ]\n",
    "\n",
    "with open('stocks.csv','w') as f:\n",
    "    f_csv = csv.DictWriter(f, headers)\n",
    "    f_csv.writeheader()\n",
    "    f_csv.writerows(rows)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.读写JSON数据\n",
    "\n",
    "json 模块提供了一种很简单的方式来编码和解码JSON数据。 其中两个主要的函数是 json.dumps() 和 json.loads() ， 要比其他序列化函数库如pickle的接口少得多。 \n",
    "\n",
    "处理字符串:\n",
    "- json.dumps()\n",
    "- json.loads()\n",
    "\n",
    "处理文件：\n",
    "- json.dump()\n",
    "- json.load()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"name\": \"ACME\", \"shares\": 100, \"price\": 542.23}'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "{'name': 'ACME', 'shares': 100, 'price': 542.23}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'ACME', 'price': 542.23, 'shares': 100}\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "import pprint\n",
    "\n",
    "data = {\n",
    "    'name' : 'ACME',\n",
    "    'shares' : 100,\n",
    "    'price' : 542.23\n",
    "}\n",
    "\n",
    "# 转成json\n",
    "data_str = json.dumps(data)\n",
    "data_str\n",
    "\n",
    "# json转成python对象\n",
    "pyobj = json.loads(data_str)\n",
    "pyobj\n",
    "\n",
    "\n",
    "with open('data.json', 'w') as f:\n",
    "    json.dump(data, f)\n",
    "\n",
    "with open('data.json', 'r') as f:\n",
    "    pydata = json.load(f)\n",
    "    pprint.pprint(pydata)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<__main__.JSONObject object at 0x7f284c84ca90>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'ACME'"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "490.1"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "50"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 下面是如何将一个JSON字典转换为一个Python对象例子\n",
    "class JSONObject:\n",
    "     def __init__(self, d):\n",
    "         self.__dict__ = d\n",
    "\n",
    "s = '{\"name\": \"ACME\", \"shares\": 50, \"price\": 490.1}'\n",
    "import json\n",
    "\n",
    "ret_data = json.loads(s, object_hook=JSONObject)\n",
    "print(ret_data)\n",
    "ret_data.name\n",
    "ret_data.price\n",
    "ret_data.shares"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.解析简单的XML数据\n",
    "\n",
    "可以使用 xml.etree.ElementTree 模块从简单的XML文档中提取数据。\n",
    "\n",
    "对于更高级的应用程序，你需要考虑使用 lxml 。 它使用了和ElementTree同样的编程接口，因此上面的例子同样也适用于lxml。 你只需要将刚开始的import语句换成 from lxml.etree import parse 就行了。 lxml 完全遵循XML标准，并且速度也非常快，同时还支持验证，XSLT，和XPath等特性。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Real Python: Python 3.11 Preview: TOML and tomllib\n",
      "Mon, 04 Jul 2022 14:00:00 +0000\n",
      "https://realpython.com/python311-tomllib/\n",
      "\n",
      "Python for Beginners: Convert String to List in Python\n",
      "Mon, 04 Jul 2022 13:00:00 +0000\n",
      "https://www.pythonforbeginners.com/uncategorized/convert-string-to-list-in-python\n",
      "\n",
      "Mike Driscoll: PyDev of the Week: Stephen Gruppetta\n",
      "Mon, 04 Jul 2022 12:30:12 +0000\n",
      "https://www.blog.pythonlibrary.org/2022/07/04/pydev-of-the-week-stephen-gruppetta/\n",
      "\n",
      "EuroPython: Humans of EuroPython:  Vicky Twomey-Lee 💖\n",
      "Mon, 04 Jul 2022 12:15:46 +0000\n",
      "https://blog.europython.eu/humans-of-europython-vicky/\n",
      "\n",
      "Łukasz Langa: Weekly Report, June 27 - July 3\n",
      "Mon, 04 Jul 2022 11:12:00 +0000\n",
      "https://lukasz.langa.pl/78771bbf-c0e6-4fff-9a7b-90fc7c8c7b77/\n",
      "\n",
      "Łukasz Langa: Weekly Report, June 20 - 26\n",
      "Mon, 04 Jul 2022 11:12:00 +0000\n",
      "https://lukasz.langa.pl/c0ab8578-f2a3-45ac-82a8-39eebb1beb23/\n",
      "\n",
      "Django Weblog: Django security releases issued: 4.0.6 and 3.2.14\n",
      "Mon, 04 Jul 2022 07:58:13 +0000\n",
      "https://www.djangoproject.com/weblog/2022/jul/04/security-releases/\n",
      "\n",
      "Zato Blog: Integrating with WordPress and Elementor API webhooks\n",
      "Mon, 04 Jul 2022 07:41:18 +0000\n",
      "https://zato.io/blog/posts/wordpress-webhooks.html\n",
      "\n",
      "Paolo Melchiorre: The PyCon Italia 2022 multiplicity\n",
      "Sun, 03 Jul 2022 22:00:00 +0000\n",
      "https://www.paulox.net/2022/07/04/the-pycon-italia-2022-multiplicity/\n",
      "\n",
      "Podcast.__init__: Tetra: A Full Stack Web Framework That Doesn't Make You Write Everything Twice\n",
      "Sun, 03 Jul 2022 21:20:29 +0000\n",
      "https://www.pythonpodcast.com/tetra-full-stack-component-web-framework-episode-369/\n",
      "\n",
      "Linux Stans: How to Install Pip on Ubuntu\n",
      "Sun, 03 Jul 2022 15:28:31 +0000\n",
      "https://linuxstans.com/install-pip-ubuntu/\n",
      "\n",
      "ItsMyCode: TypeError: &#8216;str&#8217; object does not support item assignment\n",
      "Sat, 02 Jul 2022 20:00:38 +0000\n",
      "https://itsmycode.com/typeerror-str-object-does-not-support-item-assignment/\n",
      "\n",
      "IslandT: Old code review on the earning project\n",
      "Sat, 02 Jul 2022 14:33:04 +0000\n",
      "https://islandtropicaman.com/wp/2022/07/02/old-code-review-on-the-earning-project/\n",
      "\n",
      "Mike Driscoll: An Intro to Python's Package Installer: pip (Video)\n",
      "Sat, 02 Jul 2022 12:36:53 +0000\n",
      "https://www.blog.pythonlibrary.org/2022/07/02/an-intro-to-pythons-package-installer-pip-video/\n",
      "\n",
      "The Python Coding Blog: Simulating a Tennis Match Using Object-Oriented Programming in Python—Wimbledon Special Part 1\n",
      "Sat, 02 Jul 2022 12:35:59 +0000\n",
      "https://thepythoncodingbook.com/2022/07/02/simulating-a-tennis-match-using-object-oriented-programming-in-python-wimbledon-special-part-1/\n",
      "\n",
      "ItsMyCode: ModuleNotFoundError: No module named &#8216;numpy&#8217;\n",
      "Sat, 02 Jul 2022 11:23:10 +0000\n",
      "https://itsmycode.com/modulenotfounderror-no-module-named-numpy/\n",
      "\n",
      "ItsMyCode: [Solved] ValueError: I/O operation on closed file\n",
      "Sat, 02 Jul 2022 10:42:18 +0000\n",
      "https://itsmycode.com/python-valueerror-io-operation-on-closed-file/\n",
      "\n",
      "Kay Hayen: Nuitka Release 0.9\n",
      "Sat, 02 Jul 2022 06:17:00 +0000\n",
      "https://nuitka.net/posts/nuitka-release-09.html\n",
      "\n",
      "Armin Ronacher: A Non Fungible Future\n",
      "Sat, 02 Jul 2022 00:00:00 +0000\n",
      "http://lucumr.pocoo.org/2022/7/2/non-fungible-future\n",
      "\n",
      "death and gravity: reader 2.14 released\n",
      "Fri, 01 Jul 2022 18:18:18 +0000\n",
      "https://death.andgravity.com/reader-2-14\n",
      "\n",
      "Test and Code: 191: Running your own site for fun and absolutely no profit whatsoever - Brian Wisti\n",
      "Fri, 01 Jul 2022 17:45:00 +0000\n",
      "https://testandcode.com/191\n",
      "\n",
      "EuroPython: EuroPython June 2022 Newsletter\n",
      "Fri, 01 Jul 2022 14:03:19 +0000\n",
      "https://blog.europython.eu/europython-june-2022-newsletter/\n",
      "\n",
      "Python for Beginners: Convert String to Set in Python\n",
      "Fri, 01 Jul 2022 13:00:00 +0000\n",
      "https://www.pythonforbeginners.com/basics/convert-string-to-set-in-python\n",
      "\n",
      "Ian Ozsvald: Upcoming discussion calls for Team Structure and Buidling a Backlog for data science leads\n",
      "Fri, 01 Jul 2022 12:38:10 +0000\n",
      "https://ianozsvald.com/2022/07/01/upcoming-discussion-calls-for-team-structure-and-buidling-a-backlog-for-data-science-leads/\n",
      "\n",
      "Mike Driscoll: Python 101 - An Intro to Classes (Video)\n",
      "Fri, 01 Jul 2022 12:22:22 +0000\n",
      "https://www.blog.pythonlibrary.org/2022/07/01/python-101-an-intro-to-classes-video/\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from urllib.request import urlopen\n",
    "from xml.etree.ElementTree import parse\n",
    "\n",
    "u = urlopen('http://planet.python.org/rss20.xml')\n",
    "doc = parse(u)\n",
    "\n",
    "for item in doc.iterfind('channel/item'):\n",
    "    title = item.findtext('title')\n",
    "    date = item.findtext('pubDate')\n",
    "    link = item.findtext('link')\n",
    "\n",
    "    print(title)\n",
    "    print(date)\n",
    "    print(link)\n",
    "    print()\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.增量式解析大型XML文件\n",
    "\n",
    "任何时候只要你遇到增量式的数据处理时，第一时间就应该想到迭代器和生成器。 \n",
    "\n",
    "- xml.etree.ElementTree.iterparse()\n",
    "\n",
    "不写代码了，直接看示例：\n",
    "\n",
    "https://python3-cookbook.readthedocs.io/zh_CN/latest/c06/p04_parse_huge_xml_files_incrementally.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.将字典转换为XML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'<stock><name>GOOG</name><shares>100</shares><price>490.1</price></stock>'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "b'<stock><name>&lt;spam&gt;</name></stock>'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'&lt;spam&gt;'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'<spam>'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from xml.etree.ElementTree import Element\n",
    "from xml.etree.ElementTree import tostring\n",
    "from xml.sax.saxutils import escape, unescape\n",
    "\n",
    "def dict_to_xml(tag, d):\n",
    "    elem = Element(tag)\n",
    "    for key, val in d.items():\n",
    "        child = Element(key)\n",
    "        child.text = str(val)\n",
    "        elem.append(child)\n",
    "    return elem\n",
    "\n",
    "s = { 'name': 'GOOG', 'shares': 100, 'price':490.1 }\n",
    "e = dict_to_xml('stock', s)\n",
    "tostring(e)\n",
    "\n",
    "\n",
    "# xml特殊字符转换\n",
    "print('-'*20)\n",
    "d = { 'name' : '<spam>' }\n",
    "e = dict_to_xml('stock', d)\n",
    "tostring(e)\n",
    "\n",
    "print('-'*20)\n",
    "s = '<spam>'\n",
    "ss = escape(s)\n",
    "ss\n",
    "unescape(ss)\n",
    "\n",
    "# 推荐你创建 Element 实例而不是字符串\n",
    "# Element 实例可以不用考虑解析XML文本的情况下通过多种方式被处理\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6.解析和修改XML"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "420"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<Element 'stop' at 0x7f284c716d60>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<Element 'nm' at 0x7f284c710360>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_xml_str = \"\"\"<?xml version=\"1.0\"?>\n",
    "<stop>\n",
    "    <id>14791</id>\n",
    "    <nm>Clark &amp; Balmoral</nm>\n",
    "    <sri>\n",
    "        <rt>22</rt>\n",
    "        <d>North Bound</d>\n",
    "        <dd>North Bound</dd>\n",
    "    </sri>\n",
    "    <cr>22</cr>\n",
    "    <pre>\n",
    "        <pt>5 MIN</pt>\n",
    "        <fd>Howard</fd>\n",
    "        <v>1378</v>\n",
    "        <rn>22</rn>\n",
    "    </pre>\n",
    "    <pre>\n",
    "        <pt>15 MIN</pt>\n",
    "        <fd>Howard</fd>\n",
    "        <v>1867</v>\n",
    "        <rn>22</rn>\n",
    "    </pre>\n",
    "</stop>\n",
    "\"\"\"\n",
    "pred_xml_path = '/tmp/pred.xml'\n",
    "with open(pred_xml_path, 'w') as f:\n",
    "    f.write(pred_xml_str)\n",
    "\n",
    "from xml.etree.ElementTree import parse, Element\n",
    "doc = parse(pred_xml_path)\n",
    "root = doc.getroot()\n",
    "root\n",
    "\n",
    "root.remove(root.find('sri'))\n",
    "root.remove(root.find('cr'))\n",
    "root.find('nm')\n",
    "# TODO error occurs\n",
    "# root.getchildren().index(root.find('nm'))\n",
    "\n",
    "e = Element('spam')\n",
    "e.text = 'This is a test'\n",
    "root.insert(1,e)\n",
    "\n",
    "\n",
    "doc.write('/tmp/newpred.xml')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7.利用命名空间解析XML文档\n",
    "\n",
    "跳过"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.与关系型数据库的交互\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f284c71d6c0>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f284c71d6c0>"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('GOOG', 100, 490.1)\n",
      "('AAPL', 50, 545.75)\n",
      "('FB', 150, 7.45)\n",
      "('HPQ', 75, 33.2)\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "import os\n",
    "\n",
    "stocks = [\n",
    "    ('GOOG', 100, 490.1),\n",
    "    ('AAPL', 50, 545.75),\n",
    "    ('FB', 150, 7.45),\n",
    "    ('HPQ', 75, 33.2),\n",
    "]\n",
    "\n",
    "dbpath = '/tmp/database.db'\n",
    "os.remove(dbpath)\n",
    "db = sqlite3.connect(dbpath)\n",
    "\n",
    "c = db.cursor()\n",
    "c.execute('create table portfolio (symbol text, shares integer, price real)')\n",
    "c.executemany('insert into portfolio values (?,?,?)', stocks)\n",
    "\n",
    "for row in c.execute('select * from portfolio'):\n",
    "    print(row)\n",
    "\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.编码和解码十六进制数\n",
    "\n",
    "将一个十六进制字符串解码成一个字节字符串或者将一个字节字符串编码成一个十六进制字符串。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'68656c6c6f'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "b'hello'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'68656c6c6f'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "b'68656C6C6F'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "b'hello'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'68656C6C6F'"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 函数 base64.b16decode() 和 base64.b16encode() 只能操作大写形式的十六进制字母， 而 binascii 模块中的函数大小写都能处理\n",
    "# 需要注意的是编码函数所产生的输出总是一个字节字符串。 \n",
    "# 如果想强制以Unicode形式输出，你需要增加一个额外的界面步骤。\n",
    "\n",
    "import binascii\n",
    "s = b'hello'\n",
    "h = binascii.b2a_hex(s)\n",
    "h\n",
    "binascii.a2b_hex(h)\n",
    "h.decode('ascii')\n",
    "\n",
    "import base64\n",
    "h = base64.b16encode(s)\n",
    "h\n",
    "base64.b16decode(h)\n",
    "h.decode('ascii')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10.编码解码Base64数据\n",
    "\n",
    "base64 模块中有两个函数 b64encode() and b64decode() 可以帮你解决这个问题。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "b'aGVsbG8='"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'aGVsbG8='"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "b'hello'"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "b'hello'"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = b'hello'\n",
    "\n",
    "import base64\n",
    "h = base64.b64encode(s)\n",
    "h\n",
    "h.decode('ascii')\n",
    "\n",
    "base64.b64decode(h)\n",
    "base64.b64decode(h.decode('ascii'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.读写二进制数组数据\n",
    "\n",
    "可以使用 struct 模块处理二进制数据。 \n",
    "\n",
    "- struct.Struct().pack()\n",
    "- struct.Struct().unpack()\n",
    "- struct.Struct().size\n",
    "- struct.Struct().unpack_from()\n",
    "\n",
    "剩下的，看原文：\n",
    "\n",
    "https://python3-cookbook.readthedocs.io/zh_CN/latest/c06/p11_read_write_binary_arrays_of_structures.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 12.读取嵌套和可变长二进制数据\n",
    "\n",
    "比较难，没懂。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 13.数据的累加与统计操作\n",
    "\n",
    "pandas 库。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "caf1c2fcf97217de91eafa76b907d50f9ea378f5ffbee7f571142d119bb6a771"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
